function outdat = queries_dev(conn, type, parameters)
  % Moorings-specific queries
  %
  % Author: David Goldsmith, Wash. State Dept. of Ecology, dgol461@ecy.wa.gov
  % Release date: < 9/15/2011
  
    switch type
        case 'site'
            outdat = site_query(conn, parameters{:});
        case 'PDTPF'
            [siteID, t_interval] = parameters{:};
            outdat = pdtpf_query(conn, siteID, t_interval);
        case 'equipment'
            [siteID, t_interval] = parameters{:};
            outdat = equipment_query(conn, siteID, t_interval);
        case 'RC'
            outdat = RC_query(conn);
        case 'calib'
            [equipID, calibType, UTCDDT] = parameters{:};
            outdat = calib_query(conn, equipID, calibType, UTCDDT);
        case 'joint'
            [siteID, t_interval] = parameters{:};
            outdat = joint_query(conn, siteID, t_interval);
        case 'DO'
            RVIDs = parameters{:};
            outdat = DO_query(conn, RVIDs);
        case 'inventory'
            outdat = inventory_query(conn, parameters{:});
        case 'caltyp'
            outdat = caltyp_query(conn, parameters{:});
        case 'calform'
            outdat = calform_query(conn, parameters{:});
    end
end

function outdat = site_query(conn, siteID)
    try
        sqlQuery = {['SELECT SiteID, SiteName, SiteDescription, SiteTypeID, '...
                    'LatitudeDecimal, LongitudeDecimal, SiteMLLWDecimal '],...
                    'FROM Site ',...
                    strcat('WHERE SiteID=''', siteID, '''')};

        dbmeta = get(dmd(conn));
        disp(['User ' dbmeta.UserName ' querying SiteID ' siteID ' in Site Table'])
        curs   = exec(conn, [sqlQuery{1} ' ' sqlQuery{2} ' ' sqlQuery{3}]);
        setdbprefs('DataReturnFormat','cellarray')
        curs = fetch(curs);
        close(curs)
        outdat = curs.Data;
        if length(outdat) <= 1
            throw(MException('CustomException:site_query',...
                'length(curs.Data) <= 1'));
        end
    catch ME
        outdat = {getReport(ME, 'extended', 'hyperlinks', 'off')};
    end
end

function outdat = pdtpf_query(conn, siteID, t_interval)
    try
        sqlQuery = {'SELECT ProcessedFlag, PostedUTCDatetime ',...
                    'FROM RawValue ',...
                    ['WHERE (((SiteID)=''' siteID ''') AND '...
                     '((PostedUTCDatetime)>=''' datestr(t_interval(1),'mm/dd/yyyy HH:MM:SS') ''''...
                     ' And (PostedUTCDatetime)<=''' datestr(t_interval(2),'mm/dd/yyyy HH:MM:SS') ''')) '],...
                     'ORDER BY PostedUTCDatetime;'};
        dbmeta = get(dmd(conn));
        disp(['User ' dbmeta.UserName ' querying SiteID ' siteID ' for PostedUTCDatetime and ' ...
              'ProcessedFlag s.t. ' datestr(t_interval(1),'mm/dd/yyyy HH:MM:SS') ' <= PostedUTCDatetime ' ...
              '<= ' datestr(t_interval(2),'mm/dd/yyyy HH:MM:SS') ' in RawValue Table'])
        curs   = exec(conn, [sqlQuery{1} ' ' sqlQuery{2} ' ' sqlQuery{3} ' ' sqlQuery{4}]);
        setdbprefs('DataReturnFormat', 'cellarray')
        curs = fetch(curs);
        close(curs)
        if ~isempty(curs.Message)
            throw(MException('CustomException:pdt_query', curs.Message));
        end
        outdat = curs.Data;
        if strcmp(outdat{1}, 'No Data')
            throw(MException('CustomException:pdt_query', outdat{1}));
        else
            try
                outdat(:,2) = globals_dev.trimPUTCD(outdat(:,2));
            catch
            end
        end
    catch ME
        outdat = {getReport(ME, 'extended', 'hyperlinks', 'off')};
    end
end

function outdat = equipment_query(conn, siteID, t_interval)
    try
        setdbprefs('DataReturnFormat','cellarray')
        if any(strcmp(siteID,{'MCH01SR', 'MUK01SR'}))
            curs = fetch(exec(conn, ['SELECT UTCDeployDatetime, DeploymentDepth From SiteEquipment ' ...
                                     'WHERE SiteID=''' siteID(1:5) 'BR'' AND ' ...
                                     'UTCDeployDatetime<=''' datestr(t_interval(2),'mm/dd/yyyy HH:MM:SS') ''' ' ...
                                     'ORDER BY UTCDeployDatetime']));
            if ~isempty(curs.Message)
                throw(MException('CustomException:SiteEquipment_query', curs.Message));
            end
            outdat = curs.Data;
            if strcmp(outdat{1}, 'No Data')
                throw(MException('CustomException:SiteEquipment_query', outdat{1}));
            end
            DDts = datenum(outdat(:,1)); 
            goodIndices = find(DDts > t_interval(1));
            if goodIndices(1) ~= 1
                goodIndices = [goodIndices(1)-1 goodIndices];
            end
            DDts = DDts(goodIndices);
            deployDepths = cell2mat(outdat(:,2));
            deployDepths = deployDepths(goodIndices);
            outdat = {DDts, deployDepths};
        else
%            DDts = []; deployDepths = [];
            sqlQuery = {['SELECT EquipmentID, StatusID, EquipmentName, UTCDeployDatetime, AuxillaryChannelID, '...
                        'CalibrationTypeID, ResultColumnID, SiteID'],...
                        'FROM SiteEquipment ',...
                        ['WHERE SiteID=''' siteID ''' AND '...
                         'UTCDeployDatetime<=''' datestr(t_interval(2),'mm/dd/yyyy HH:MM:SS') ''''],...
                         'ORDER BY UTCDeployDatetime'};
            curs   = exec(conn, [sqlQuery{1} ' ' sqlQuery{2} ' ' sqlQuery{3} ' ' sqlQuery{4}]);
            curs = fetch(curs);
            close(curs)
            if ~isempty(curs.Message)
                throw(MException('CustomException:SiteEquipment_query', curs.Message));
            end
            outdat = curs.Data;
            if strcmp(outdat{1}, 'No Data')
                throw(MException('CustomException:SiteEquipment_query', outdat{1}));
            end
        end
%        outdat = {outdat, {DDts, deployDepths}};
    catch ME
        outdat = {getReport(ME, 'extended', 'hyperlinks', 'off')};
    end
end

function outdat = RC_query(conn)
    try
        sqlQuery = 'SELECT ResultColumnID, ResultColumnName FROM ResultColumn';
        dbmeta = get(dmd(conn));
        curs   = exec(conn, sqlQuery);
        setdbprefs('DataReturnFormat','cellarray')
        curs = fetch(curs);
        close(curs)
        if ~isempty(curs.Message)
            throw(MException('CustomException:SiteEquipment_query', curs.Message));
        end
        outdat = curs.Data;
        if strcmp(outdat{1}, 'No Data')
            throw(MException('CustomException:SiteEquipment_query', outdat{1}));
        end
    catch ME
        outdat = {getReport(ME, 'extended', 'hyperlinks', 'off')};
    end
end

function outdat = calib_query(conn, equipID, calibType, UTCDDT)
    try
        outdat = cell(20,1);
        dbmeta = get(dmd(conn));
        setdbprefs('DataReturnFormat','cellarray')
        nr = 0;
        for i=1:length(equipID)
            sqlQuery = {['SELECT EquipmentID, UTCCalibrationBeginDatetime, UTCCalibrationEndDatetime, ',...
                    'EquipmentName, CalibrationTypeID, CalibrationFormulaID, CalibrationID, ',...
                    'C1Quantity, C2Quantity, C3Quantity, C4Quantity, C5Quantity, C9Quantity, ',...
                    'C6Quantity, C7Quantity, C8Quantity, C10Quantity, C11Quantity, C12Quantity, ',...
                    'C13Quantity'],...
                    'FROM Calibration ',...
                    ['WHERE EquipmentID=''' num2str(equipID{i}) ''' AND CalibrationTypeID=''' calibType{i}...
                     ''' AND UTCCalibrationBeginDatetime<=''' datestr(UTCDDT{i},'mm/dd/yyyy HH:MM:SS') ''''],...
                     'ORDER BY UTCCalibrationBeginDatetime'};
            curs = exec(conn, [sqlQuery{1} ' ' sqlQuery{2} ' ' sqlQuery{3} ' ' sqlQuery{4}]);
            curs = fetch(curs);
            if ~isempty(curs.Message)
                throw(MException('CustomException:Calibration_query', curs.Message));
            end
            tmp = curs.Data;
            for j=1:length(tmp(:,1))
                nr = nr + 1;
                outdat(:,nr) = tmp(j,:);
            end
        end
        outdat = outdat';
        close(curs)
        if strcmp(outdat{1}, 'No Data')
            throw(MException('CustomException:Calibration_query', outdat{1}));
        end
    catch ME
        outdat = {getReport(ME, 'extended', 'hyperlinks', 'off')};
    end
end

function outdat = joint_query(conn, siteID, t_interval)
    try
        sqlQuery = {['SELECT RawValue.RawValueID, '...
                            'RawValue.SiteID, '...
                            'RawValue.PostedUTCDatetime, '...
                            'RawValue.PressureQuantity, '...
                            'RawValue.TemperatureQuantity, '...
                            'RawValue.ConductivityQuantity, '...
                            'RawValue.SalinityQuantity, '...
                            'RawValue.Aux0Quantity, '...
                            'CalculatedValue.DensityCalculated, '...
                            'CalculatedValue.DOSaturationCalculated'],...
                    ['FROM RawValue LEFT OUTER JOIN CalculatedValue ON RawValue.RawValueID = '...
                     'CalculatedValue.RawValueID'],...
                    ['WHERE RawValue.SiteID=''' siteID ''' AND '...
                     '(PostedUTCDatetime>=''' datestr(t_interval(1),'mm/dd/yyyy HH:MM:SS') ''''...
                     ' AND PostedUTCDatetime<=''' datestr(t_interval(2),'mm/dd/yyyy HH:MM:SS') ''')'],...
                     'ORDER BY RawValue.PostedUTCDatetime;'};
        dbmeta = get(dmd(conn));
        curs   = exec(conn, [sqlQuery{1} ' ' sqlQuery{2} ' ' sqlQuery{3} ' ' sqlQuery{4}]);
        setdbprefs('DataReturnFormat','cellarray')
        curs = fetch(curs);
        close(curs)
        if ~isempty(curs.Message)
            throw(MException('CustomException:joint_query', curs.Message));
        end
        outdat = curs.Data;
        if strcmp(outdat{1}, 'No Data')
            throw(MException('CustomException:joint_query', outdat{1}));
        else
            try
                outdat(:,3) = globals_dev.trimPUTCD(outdat(:,3));
            catch
            end
        end
    catch ME
        outdat = {getReport(ME, 'extended', 'hyperlinks', 'off')};
    end
end

function outdat = DO_query(conn, RVIDs)
    try
        sqlQuery = {'SELECT C.DOCalculated', ...
                    ['FROM RawValue R LEFT OUTER JOIN CalculatedValue C ' ...
                     'ON R.RawValueID = C.RawValueID'], ...
                    ['WHERE C.RawValueID IN (' num2str(RVIDs(1:end-1), '%d, ') num2str(RVIDs(end)) ')'], ...
                     'ORDER BY R.PostedUTCDatetime'};
        dbmeta = get(dmd(conn));
        curs   = exec(conn, [sqlQuery{1} ' ' sqlQuery{2} ' ' sqlQuery{3} ' ' sqlQuery{4}]);
        setdbprefs('DataReturnFormat','cellarray')
        curs = fetch(curs);
        close(curs)
        if ~isempty(curs.Message)
            throw(MException('CustomException:DO_query', curs.Message));
        end
        outdat = curs.Data;
        if strcmp(outdat{1}, 'No Data')
            throw(MException('CustomException:DO_query', outdat{1}));
        elseif length(outdat) ~= length(RVIDs)
            throw(MException('CustomException:DO_query', ...
                             'RawValueID/Dissolved Oxygen counts unequal; database corruption likely!'));            
        end
    catch ME
        outdat = {getReport(ME, 'extended', 'hyperlinks', 'off')};
    end
end

function outdat = inventory_query(conn, equipID)
    try
        where = []; cnt = 1; Where = 'WHERE ';
        for i=1:length(equipID)
            if ~ismember(equipID{i}, where)
                Where = [Where, 'EquipmentID=', num2str(equipID{i}), ' OR '];
                where(cnt) = equipID{i};
                cnt = cnt + 1;
            end
        end
        Where = Where(1:end-4);
        sqlQuery = {strcat('SELECT EquipmentName, SerialNumberDescription, ',...
                    'EquipmentTypeID, ManufacturerName, ModelDescription'),...
                    'FROM EquipmentInventory ',...
                    Where};

        dbmeta = get(dmd(conn));
        disp(['User ' dbmeta.UserName ' querying EquipmentID ' num2str([equipID{:}]) ...
              ' in EquipmentInventory Table'])
        curs   = exec(conn, [sqlQuery{1} ' ' sqlQuery{2} ' ' sqlQuery{3}]);
        setdbprefs('DataReturnFormat','cellarray')
        curs = fetch(curs);
        close(curs)
        outdat = curs.Data;
        if length(outdat) <= 1
            throw(MException('CustomException:EquipmentInventory_query',...
                'length(curs.Data) <= 1'));
        end
    catch ME
        outdat = getReport(ME, 'extended', 'hyperlinks', 'off');
    end
end

function outdat = caltyp_query(conn, calibType)
    try
        where_calibType = {}; cnt = 1; Where_calibType = 'WHERE ';
        for i=1:length(calibType)
            if ~ismember(calibType(i), where_calibType)
                Where_calibType = [Where_calibType, 'CalibrationTypeID=''', calibType{i}, ''' OR '];
                where_calibType(cnt) = calibType(i);
                cnt = cnt + 1;
            end
        end
        Where = Where_calibType(1:end-4);

        sqlQuery = {'SELECT CalibrationTypeID, CalibrationTypeDescription',...
                    'FROM CalibrationType ',...
                    Where};

        dbmeta = get(dmd(conn));
        disp(['User ' dbmeta.UserName ' querying CalibrationTypeID ' calibType{:} ' in CalibrationType Table'])
        curs   = exec(conn, [sqlQuery{1} ' ' sqlQuery{2} ' ' sqlQuery{3}]);
        setdbprefs('DataReturnFormat','cellarray')
        curs = fetch(curs);
        close(curs)
        if strcmp(curs.Data{:}, 'No Data')
            throw(MException('CustomException:CalibrationType_query',...
                'curs.Data{:} = ''No Data'''));
        elseif length(curs.Data) <= 1
            throw(MException('CustomException:CalibrationType_query',...
                'length(curs.Data) <= 1'));
        else
            outdat = curs.Data;
        end
    catch ME
        outdat = getReport(ME, 'extended', 'hyperlinks', 'off');
    end
end

function outdat = calform_query(conn, calibForm)
    try
        where_calibForm = {}; cnt = 1; Where_calibForm = 'WHERE ';
        for i=1:length(calibForm)
            if ~ismember(calibForm(i), where_calibForm)
                Where_calibForm = [Where_calibForm, 'CalibrationFormulaID=''', calibForm{i}, ''' OR '];
                where_calibForm(cnt) = calibForm(i);
                cnt = cnt + 1;
            end
        end
        Where = Where_calibForm(1:end-4);
        
        sqlQuery = {'SELECT CalibrationFormulaID, FormulaName, FormulaDescription, StoredProcedureName, UnitsDescription',...
                    'FROM CalibrationFormula ',...
                    Where};

        dbmeta = get(dmd(conn));
        disp(['User ' dbmeta.UserName ' querying CalibrationFormulaID ' calibForm{:} ' in CalibrationFormula Table'])
        curs   = exec(conn, [sqlQuery{1} ' ' sqlQuery{2} ' ' sqlQuery{3}]);
        setdbprefs('DataReturnFormat','cellarray')
        curs = fetch(curs);
        close(curs)
        if strcmp(curs.Data{:}, 'No Data')
            throw(MException('CustomException:CalibrationFormula_query',...
                'curs.Data{:} = ''No Data'''));
        elseif length(curs.Data) <= 1
            throw(MException('CustomException:CalibrationFormula_query',...
                'length(curs.Data) <= 1'));
        else
            outdat = curs.Data;
        end
    catch ME
        outdat = getReport(ME, 'extended', 'hyperlinks', 'off');
    end
end